package repositories

import (
	"log"

	"gitee.com/yanxingshuyuan/backend/server/models"
	g "github.com/doug-martin/goqu/v9"
	"github.com/jmoiron/sqlx"
)

// NoteRepo all kinds of notes
type NoteRepo struct {
	db      *sqlx.DB
	matRepo *MatRepo
}

func NewNoteRepo(db *sqlx.DB, matRepo *MatRepo) *NoteRepo {
	return &NoteRepo{db: db, matRepo: matRepo}
}

func (r *NoteRepo) AddStar(n *models.ONote) bool {
	n.Kind = int(models.NoteKindStar)
	return r.AddNoteOld(n)
}

func (r *NoteRepo) AddNote(n *models.NoteForm) bool {
	log.Printf("Add note: %#v\n", n)
	// 先保存对应的素材
	mat := n.Mat
	matForm := &models.MatForm{Mat: mat}
	matForm.Pids = n.Author
	r.matRepo.AddMat(matForm)

	// 获取新的素材ID
	mid := matForm.ID
	log.Printf("New Mid: %v\n", mid)

	isRange := n.S != n.E
	// 保存起始位置
	if n.S.MID == "" {
		n.S.MID = n.MID
	}
	r.saveSpot(&n.S)

	// 保存终止位置
	if n.E.MID == "" {
		n.E.MID = n.MID
	}
	if isRange {
		r.saveSpot(&n.E)
	} else { // 注意在保存之后spot的内容会发生变化，所以需要再同步一下
		n.E = n.S
	}
	// 保存笔记素材到位置的关联
	r.saveNote(mid, n.S.ID, n.E.ID, n.Section, n.Scope)
	return true
}

func (r *NoteRepo) saveSpot(spot *models.Spot) bool {

	sql :=
		`WITH input_rows(mid, line, pos) 
		AS (VALUES ($1::char(8), $2::integer, $3::integer))
	 	, ins AS (
			INSERT INTO spots (mid, line, pos) 
			SELECT * FROM input_rows ON CONFLICT ON CONSTRAINT spots_uniq DO NOTHING
			RETURNING id              
		)
	 	SELECT id FROM ins UNION  ALL SELECT s.id as id FROM input_rows JOIN spots s USING (mid, line, pos);`

	err := r.db.Get(&spot.ID, sql, spot.MID, spot.Line, spot.Pos)
	if err != nil {
		log.Printf("Error add spot: %#v, with error: %v\n", spot, err)
		return false
	}
	return true
}
func (r *NoteRepo) saveNote(mid string, sid string, eid string, section int, scope int) bool {
	_, err := r.db.Exec("INSERT INTO notes (mid, s, e, section, scope) VALUES ($1, $2, $3, $4, $5) ON CONFLICT ON CONSTRAINT notes_uniq DO NOTHING", mid, sid, eid, section, scope)
	if err != nil {
		log.Printf("Error add note %v, %v, %v, %v, %v, with error: %v\n", mid, sid, eid, section, scope, err)
		return false
	}
	return true
}

func (r *NoteRepo) AddNoteOld(n *models.ONote) bool {
	log.Printf("Add note: %#v\n", n)
	// 利用CTE，在DO NOTHING时也能返回ID，参看<https://stackoverflow.com/a/42217872>
	// TODO: 由于这里必须指定参数的类型，但sqlx命名参数的语法是':name'，解析时和参数类型转换'::type'冲突了，因此暂时只能用简单的$1参数
	sql :=
		`WITH input_rows(kind, scope, pid, mid, line, spos, epos, content, value) 
		AS (VALUES ($1::smallint, $2::smallint, $3::char(8), $4::bigint, $5::integer, $6::smallint, $7::smallint, $8::text, $9::integer))
	 	, ins AS (
			INSERT INTO notes (kind, scope, pid, mid, line, spos, epos, content, value) 
			SELECT * FROM input_rows ON CONFLICT ON CONSTRAINT notes_constraint_uniq DO NOTHING
			RETURNING id              
		)
	 	SELECT id FROM ins
	 	UNION  ALL
		SELECT n.id as id FROM input_rows JOIN notes n USING (kind, scope, pid, mid, line, spos, epos);`

	err := r.db.Get(&n.ID, sql, n.Kind, n.Scope, n.PID, n.MID, n.Line, n.Spos, n.Epos, n.Content, n.Value)
	if err != nil {
		log.Printf("error adding note:%v, with err: %v\n", n, err)
		return false
	}

	// 添加星数
	if n.Kind == int(models.NoteKindStar) && n.Scope == int(models.ScopeArticle) { // 收藏操作不需要添加星数，因为默认是1
		return true
	} else {
		r.incStarCount(n)
	}

	// 笔记所属的素材热度+1
	r.incHeat(n.MID)
	return true
}

func (r *NoteRepo) incStarCount(note *models.ONote) {
	// TODO: 先只给Article级别添加星数
	mid := note.MID
	pid := note.PID // 假定已经从jwt中获取到PID并填入note中

	_, err := r.db.Exec("UPDATE notes set value = value + 1 where mid = $1 and kind = $2 and scope = $3 and pid = $4", mid, models.NoteKindStar, models.ScopeArticle, pid)
	if err != nil {
		log.Printf("Error increasing star counts for note %#v\n", note)
	}
	return
}

func (r *NoteRepo) incHeat(mid string) {
	log.Printf("increasing heat for mat: %v\n", mid)
	_, err := r.db.Exec("UPDATE mats set heat = heat + 1 where id = $1", mid)
	if err != nil {
		log.Printf("Error increasing heat for mat:%v, with err:%#v\n", mid, err)
	}
}

func (r *NoteRepo) GetStars(noteid int64) int {
	var value int
	err := r.db.Get(&value, "SELECT value from notes where id = $1", noteid)
	if err != nil {
		log.Printf("Error getting stars for %v, with err: %v\n", noteid, err)
		return 0
	}
	return value
}

func (r *NoteRepo) UpdateStars(noteid int64, delta int) bool {
	_, err := r.db.Exec("UPDATE notes set value = value + $1 where id = $2", delta, noteid)
	if err != nil {
		log.Printf("error updating star:%v, with err: %v\n", noteid, err)
		return false
	}
	return true
}

// 暂时只修改内容，需要增添对其他字段的修改
func (r *NoteRepo) ModifyNote(n *models.ONote) bool {
	_, err := r.db.NamedExec("UPDATE notes set content = :content where id = :id", n)
	if err != nil {
		log.Printf("error modifying note:%v, with err: %v\n", n, err)
		return false
	}
	return true
}

func (r *NoteRepo) RemoveNote(noteid int64) {
	_, err := r.db.Exec("DELETE FROM notes where id = $1", noteid)
	if err != nil {
		log.Printf("error removing note from db:%v, with err:%v\n", noteid, err)
	}
}

func (r *NoteRepo) ListNotesForPid(pid string, mid string) (notes []models.NoteForm) {

	// 先获取mid对应的所有位置（spot）
	var spots []models.Spot
	err := r.db.Select(&spots, "SELECT * from spots where mid = $1", mid)
	if err != nil {
		log.Printf("error finding spots for mid %v, with err:%v\n", mid, err)
		return notes
	}
	var spotids []string
	for s := range spots {
		spotids = append(spotids, spots[s].ID)
	}
	if len(spotids) == 0 {
		notes = []models.NoteForm{}
		return
	}
	// 根据spot搜索notes
	ds := g.From(g.T("notes").As("n")).Where(g.ExOr{"n.s": spotids, "n.e": spotids})
	if pid != "" {
		ds = ds.Join(g.T("mats_people").As("mp"), g.On(g.Ex{"mp.mid": g.I("n.mid")})).Where(g.Ex{"mp.pid": pid})
	}
	ds = ds.Select(g.I("n.*"))
	sql, _, _ := ds.ToSQL()
	log.Printf("sql: %v\n", sql)
	var ns []models.Note
	err = r.db.Select(&ns, sql)
	if err != nil {
		log.Printf("error listing notes from db:%v, with err:%v\n", pid+"_"+mid, err)
	}

	// 根据noteid找到对应的mat
	var notids []string
	for n := range ns {
		notids = append(notids, ns[n].MID)
	}
	ds = g.From(g.T("mats").As("m")).Where(g.Ex{"m.id": notids})
	ds = ds.Select(g.I("m.*"))
	sql, _, _ = ds.ToSQL()
	log.Printf("sql: %v\n", sql)
	var ms []models.Mat
	err = r.db.Select(&ms, sql)
	if err != nil {
		log.Printf("error listing note_mats from db:%v, with err:%v\n", pid+"_"+mid, err)
	}
	var noteids []string
	for m := range ms {
		noteids = append(noteids, ms[m].ID)
		note := models.NoteForm{
			Mat: ms[m],
		}
		var s, e string
		for n := range ns {
			if ns[n].MID == ms[m].ID {
				s = ns[n].S
				e = ns[n].E
				note.Section = ns[n].Section
				note.Scope = ns[n].Scope
			}
		}
		for i := range spots {
			sp := spots[i]
			if sp.ID == s {
				note.S = sp
			}
			if sp.ID == e {
				note.E = sp
			}
		}
		notes = append(notes, note)
	}

	for i := range notes {
		mid := notes[i].Mat.ID
		ds = g.From(g.T("people").As("p")).Join(g.T("mats_people").As("mp"), g.On(g.Ex{"mp.pid": g.I("p.id")})).Where(g.Ex{"mp.mid": mid})
		ds = ds.Select(g.I("p.*"))
		sql, _, _ = ds.ToSQL()
		log.Printf("sql: %v\n", sql)
		err = r.db.Select(&notes[i].Authors, sql)
		if err != nil {
			log.Printf("error getting authors for notes: %v\n", err)
		}
	}

	return
}

func (r *NoteRepo) ListStarsForMat(pid string, mids []int64) []models.ONote {
	notes := []models.ONote{}

	query, args, err := sqlx.In("SELECT * from notes where pid = ? and mid in (?) and kind = ? and scope = ?", pid, mids, models.KindArticle, models.ScopeArticle)
	if err != nil {
		log.Printf("error making in query from db:%v[%#v], with err:%v\n", pid, mids, err)
	}
	query = r.db.Rebind(query)
	err = r.db.Select(&notes, query, args...)
	if err != nil {
		log.Printf("error listing notes from db:%v[%#v], with err:%v\n", pid, mids, err)
	}
	return notes
}

func (r *NoteRepo) ListStarsForArticles(pid string, artids []string) []models.ONote {
	notes := []models.ONote{}

	query, args, err := sqlx.In("SELECT * from notes where pid = ? and artid in (?) and kind = ? and scope = ?", pid, artids, models.KindArticle, models.ScopeArticle)
	if err != nil {
		log.Printf("error making in query from db:%v[%#v], with err:%v\n", pid, artids, err)
	}
	query = r.db.Rebind(query)
	err = r.db.Select(&notes, query, args...)
	if err != nil {
		log.Printf("error listing notes from db:%v[%#v], with err:%v\n", pid, artids, err)
	}
	return notes
}

func (r *NoteRepo) ListNotes(colid string, artid string) []models.ONote {
	notes := []models.ONote{}
	err := r.db.Select(&notes, "SELECT * from notes where colid = $1 and artid = $2", colid, artid)
	if err != nil {
		log.Printf("error listing notes from db:%v, with err:%v\n", colid+":"+artid, err)
	}
	return notes
}

func (r *NoteRepo) ListNoteCards(mid int64, scope int, line int) []models.NoteCard {
	cards := []models.NoteCard{}
	log.Printf("mid: %v, scope: %v\n", mid, scope)
	if line == -1 {
		err := r.db.Select(&cards, "SELECT n.*, p.fullname as pname from notes n, people p where n.pid = p.id and n.mid = $1 and n.scope = $2", mid, scope)
		if err != nil {
			log.Printf("error listing notes from mat: %v, with err:%v\n", mid, err)
		}
	} else {
		err := r.db.Select(&cards, "SELECT n.*, p.fullname as pname from notes n, people p where n.pid = p.id and n.mid = $1 and n.scope = $2 and n.line = $3", mid, scope, line)
		if err != nil {
			log.Printf("error listing notes from mat: %v, with err:%v\n", mid, err)
		}
	}
	return cards
}

func (r *NoteRepo) ListAllSections() (sections []models.Section) {
	err := r.db.Select(&sections, "SELECT s.id, s.name, ss.scope, ss.ord from sections s, scope_sections ss where s.id = ss.secid")
	if err != nil {
		log.Printf("error listing all sections, with error %v\n", err)
	}
	return
}
